{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T11:41:29.421864Z",
     "iopub.status.busy": "2021-05-29T11:41:29.421864Z",
     "iopub.status.idle": "2021-05-29T11:41:30.339733Z",
     "shell.execute_reply": "2021-05-29T11:41:30.337730Z",
     "shell.execute_reply.started": "2021-05-29T11:41:29.421864Z"
    }
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from pandas.tseries.offsets import Hour,Minute,Day\n",
    "from datetime import datetime\n",
    "from dateutil.parser import parse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T11:41:30.346741Z",
     "iopub.status.busy": "2021-05-29T11:41:30.345743Z",
     "iopub.status.idle": "2021-05-29T11:41:30.355742Z",
     "shell.execute_reply": "2021-05-29T11:41:30.353741Z",
     "shell.execute_reply.started": "2021-05-29T11:41:30.346741Z"
    }
   },
   "outputs": [],
   "source": [
    "cwd = os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T11:41:30.947855Z",
     "iopub.status.busy": "2021-05-29T11:41:30.947855Z",
     "iopub.status.idle": "2021-05-29T11:41:30.961854Z",
     "shell.execute_reply": "2021-05-29T11:41:30.959856Z",
     "shell.execute_reply.started": "2021-05-29T11:41:30.947855Z"
    }
   },
   "outputs": [],
   "source": [
    "data_dir = os.path.join(cwd,\"数据\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T11:41:31.450821Z",
     "iopub.status.busy": "2021-05-29T11:41:31.449820Z",
     "iopub.status.idle": "2021-05-29T11:41:31.466383Z",
     "shell.execute_reply": "2021-05-29T11:41:31.464382Z",
     "shell.execute_reply.started": "2021-05-29T11:41:31.450821Z"
    }
   },
   "outputs": [],
   "source": [
    "data_files = [file for file in os.listdir(data_dir) if os.path.splitext(file)[1] in ['.xlsx','.csv']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T11:41:31.906957Z",
     "iopub.status.busy": "2021-05-29T11:41:31.905956Z",
     "iopub.status.idle": "2021-05-29T11:41:31.944037Z",
     "shell.execute_reply": "2021-05-29T11:41:31.941035Z",
     "shell.execute_reply.started": "2021-05-29T11:41:31.906957Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>flight</th>\n",
       "      <th>deptime</th>\n",
       "      <th>depoff</th>\n",
       "      <th>arrtime</th>\n",
       "      <th>arroff</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA0001</td>\n",
       "      <td>755</td>\n",
       "      <td>-400</td>\n",
       "      <td>1038</td>\n",
       "      <td>-700</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA0002</td>\n",
       "      <td>1405</td>\n",
       "      <td>-700</td>\n",
       "      <td>2159</td>\n",
       "      <td>-400</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA0003</td>\n",
       "      <td>1740</td>\n",
       "      <td>-400</td>\n",
       "      <td>2025</td>\n",
       "      <td>-700</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA0004</td>\n",
       "      <td>840</td>\n",
       "      <td>-700</td>\n",
       "      <td>1640</td>\n",
       "      <td>-400</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA0005</td>\n",
       "      <td>910</td>\n",
       "      <td>-400</td>\n",
       "      <td>1202</td>\n",
       "      <td>-700</td>\n",
       "      <td>EDB</td>\n",
       "      <td>MBY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   flight  deptime  depoff  arrtime  arroff origin destination\n",
       "0  AA0001      755    -400     1038    -700    EDB         TFB\n",
       "1  AA0002     1405    -700     2159    -400    TFB         EDB\n",
       "2  AA0003     1740    -400     2025    -700    EDB         TFB\n",
       "3  AA0004      840    -700     1640    -400    TFB         EDB\n",
       "4  AA0005      910    -400     1202    -700    EDB         MBY"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "schedual = pd.read_csv(os.path.join(data_dir,data_files[4]))\n",
    "schedual.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T11:41:32.643790Z",
     "iopub.status.busy": "2021-05-29T11:41:32.642790Z",
     "iopub.status.idle": "2021-05-29T11:41:32.659387Z",
     "shell.execute_reply": "2021-05-29T11:41:32.657520Z",
     "shell.execute_reply.started": "2021-05-29T11:41:32.643790Z"
    }
   },
   "outputs": [],
   "source": [
    "deptime = schedual.deptime\n",
    "depoff = schedual.depoff\n",
    "arrtime = schedual.arrtime\n",
    "arroff = schedual.arroff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T11:41:40.844651Z",
     "iopub.status.busy": "2021-05-29T11:41:40.843651Z",
     "iopub.status.idle": "2021-05-29T11:41:40.868654Z",
     "shell.execute_reply": "2021-05-29T11:41:40.866653Z",
     "shell.execute_reply.started": "2021-05-29T11:41:40.844651Z"
    }
   },
   "outputs": [],
   "source": [
    "def process_time(time,off):\n",
    "    time = time.astype(str)\n",
    "    off = off.astype(str)\n",
    "    time = time.apply(lambda x:(4 - len(x))*'0') + time\n",
    "    off = '-' + off.apply(lambda x:(4 - len(x[1:]))*'0') + off.apply(lambda x:x[1:])\n",
    "    time = time.apply(lambda x:'%s:%s'%(x[:-2],x[-2:])).apply(parse)\n",
    "    off = off.apply(lambda x:Hour(int(x[:-2]))+Minute(int(x[-2:])))\n",
    "    return time,off"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T11:41:42.658199Z",
     "iopub.status.busy": "2021-05-29T11:41:42.657199Z",
     "iopub.status.idle": "2021-05-29T11:41:42.892791Z",
     "shell.execute_reply": "2021-05-29T11:41:42.890791Z",
     "shell.execute_reply.started": "2021-05-29T11:41:42.658199Z"
    }
   },
   "outputs": [],
   "source": [
    "dep_time,dep_off = process_time(deptime,depoff)\n",
    "arr_time,arr_off = process_time(arrtime,arroff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T11:41:43.516032Z",
     "iopub.status.busy": "2021-05-29T11:41:43.516032Z",
     "iopub.status.idle": "2021-05-29T11:41:43.578036Z",
     "shell.execute_reply": "2021-05-29T11:41:43.576035Z",
     "shell.execute_reply.started": "2021-05-29T11:41:43.516032Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Anaconda3\\lib\\site-packages\\pandas\\core\\arrays\\datetimelike.py:1342: PerformanceWarning: Adding/subtracting object-dtype array to DatetimeArray not vectorized\n",
      "  warnings.warn(\n"
     ]
    }
   ],
   "source": [
    "dep_ucl = dep_time - dep_off\n",
    "arr_ucl = arr_time - arr_off\n",
    "arr_ucl[dep_ucl > arr_ucl] = arr_ucl[dep_ucl > arr_ucl] + Day(1)\n",
    "arr_time = arr_ucl + arr_off\n",
    "delta_time = abs(arr_ucl - dep_ucl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T12:50:16.098954Z",
     "iopub.status.busy": "2021-05-29T12:50:16.097953Z",
     "iopub.status.idle": "2021-05-29T12:50:16.116075Z",
     "shell.execute_reply": "2021-05-29T12:50:16.114566Z",
     "shell.execute_reply.started": "2021-05-29T12:50:16.098954Z"
    }
   },
   "outputs": [],
   "source": [
    "new_schedual = schedual.loc[:,['flight','origin','destination']].copy()\n",
    "# new_schedual['Flight'] = new_schedual.flight + new_schedual.origin + new_schedual.destination\n",
    "# new_schedual = new_schedual.drop(axis=1,columns=new_schedual.columns[:3])\n",
    "new_schedual['deptime'] = dep_time\n",
    "new_schedual['arrtime'] = arr_time\n",
    "new_schedual['dep_ucl'] = dep_ucl\n",
    "new_schedual['arr_ucl'] = arr_ucl\n",
    "# new_schedual['delta_time'] = delta_time.apply(lambda x:x.seconds/60)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T12:50:17.442667Z",
     "iopub.status.busy": "2021-05-29T12:50:17.442667Z",
     "iopub.status.idle": "2021-05-29T12:50:17.487729Z",
     "shell.execute_reply": "2021-05-29T12:50:17.485729Z",
     "shell.execute_reply.started": "2021-05-29T12:50:17.442667Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "      <th>deptime</th>\n",
       "      <th>arrtime</th>\n",
       "      <th>dep_ucl</th>\n",
       "      <th>arr_ucl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA0001</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021-05-29 07:55:00</td>\n",
       "      <td>2021-05-29 10:38:00</td>\n",
       "      <td>2021-05-29 11:55:00</td>\n",
       "      <td>2021-05-29 17:38:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA0002</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021-05-29 14:05:00</td>\n",
       "      <td>2021-05-29 21:59:00</td>\n",
       "      <td>2021-05-29 21:05:00</td>\n",
       "      <td>2021-05-30 01:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA0003</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021-05-29 17:40:00</td>\n",
       "      <td>2021-05-29 20:25:00</td>\n",
       "      <td>2021-05-29 21:40:00</td>\n",
       "      <td>2021-05-30 03:25:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA0004</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021-05-29 08:40:00</td>\n",
       "      <td>2021-05-29 16:40:00</td>\n",
       "      <td>2021-05-29 15:40:00</td>\n",
       "      <td>2021-05-29 20:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA0005</td>\n",
       "      <td>EDB</td>\n",
       "      <td>MBY</td>\n",
       "      <td>2021-05-29 09:10:00</td>\n",
       "      <td>2021-05-29 12:02:00</td>\n",
       "      <td>2021-05-29 13:10:00</td>\n",
       "      <td>2021-05-29 19:02:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>810</th>\n",
       "      <td>BA2645</td>\n",
       "      <td>QEY</td>\n",
       "      <td>TNG</td>\n",
       "      <td>2021-05-29 17:05:00</td>\n",
       "      <td>2021-05-29 18:50:00</td>\n",
       "      <td>2021-05-30 00:05:00</td>\n",
       "      <td>2021-05-30 01:50:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>811</th>\n",
       "      <td>BA2670</td>\n",
       "      <td>TFB</td>\n",
       "      <td>ZMX</td>\n",
       "      <td>2021-05-29 13:35:00</td>\n",
       "      <td>2021-05-29 14:35:00</td>\n",
       "      <td>2021-05-29 20:35:00</td>\n",
       "      <td>2021-05-29 21:35:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>812</th>\n",
       "      <td>BA2700</td>\n",
       "      <td>NGS</td>\n",
       "      <td>MBY</td>\n",
       "      <td>2021-05-29 20:16:00</td>\n",
       "      <td>2021-05-29 22:30:00</td>\n",
       "      <td>2021-05-30 03:16:00</td>\n",
       "      <td>2021-05-30 05:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>813</th>\n",
       "      <td>AA2701</td>\n",
       "      <td>BOD</td>\n",
       "      <td>MBT</td>\n",
       "      <td>2021-05-29 06:45:00</td>\n",
       "      <td>2021-05-29 12:43:00</td>\n",
       "      <td>2021-05-29 14:45:00</td>\n",
       "      <td>2021-05-29 19:43:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>814</th>\n",
       "      <td>AA2702</td>\n",
       "      <td>IOM</td>\n",
       "      <td>CMJ</td>\n",
       "      <td>2021-05-29 23:55:00</td>\n",
       "      <td>2021-05-30 09:35:00</td>\n",
       "      <td>2021-05-30 09:55:00</td>\n",
       "      <td>2021-05-30 16:35:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>815 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     flight origin destination             deptime             arrtime  \\\n",
       "0    AA0001    EDB         TFB 2021-05-29 07:55:00 2021-05-29 10:38:00   \n",
       "1    AA0002    TFB         EDB 2021-05-29 14:05:00 2021-05-29 21:59:00   \n",
       "2    AA0003    EDB         TFB 2021-05-29 17:40:00 2021-05-29 20:25:00   \n",
       "3    AA0004    TFB         EDB 2021-05-29 08:40:00 2021-05-29 16:40:00   \n",
       "4    AA0005    EDB         MBY 2021-05-29 09:10:00 2021-05-29 12:02:00   \n",
       "..      ...    ...         ...                 ...                 ...   \n",
       "810  BA2645    QEY         TNG 2021-05-29 17:05:00 2021-05-29 18:50:00   \n",
       "811  BA2670    TFB         ZMX 2021-05-29 13:35:00 2021-05-29 14:35:00   \n",
       "812  BA2700    NGS         MBY 2021-05-29 20:16:00 2021-05-29 22:30:00   \n",
       "813  AA2701    BOD         MBT 2021-05-29 06:45:00 2021-05-29 12:43:00   \n",
       "814  AA2702    IOM         CMJ 2021-05-29 23:55:00 2021-05-30 09:35:00   \n",
       "\n",
       "                dep_ucl             arr_ucl  \n",
       "0   2021-05-29 11:55:00 2021-05-29 17:38:00  \n",
       "1   2021-05-29 21:05:00 2021-05-30 01:59:00  \n",
       "2   2021-05-29 21:40:00 2021-05-30 03:25:00  \n",
       "3   2021-05-29 15:40:00 2021-05-29 20:40:00  \n",
       "4   2021-05-29 13:10:00 2021-05-29 19:02:00  \n",
       "..                  ...                 ...  \n",
       "810 2021-05-30 00:05:00 2021-05-30 01:50:00  \n",
       "811 2021-05-29 20:35:00 2021-05-29 21:35:00  \n",
       "812 2021-05-30 03:16:00 2021-05-30 05:30:00  \n",
       "813 2021-05-29 14:45:00 2021-05-29 19:43:00  \n",
       "814 2021-05-30 09:55:00 2021-05-30 16:35:00  \n",
       "\n",
       "[815 rows x 7 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_schedual"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-29T12:50:43.329229Z",
     "iopub.status.busy": "2021-05-29T12:50:43.328226Z",
     "iopub.status.idle": "2021-05-29T12:50:43.713456Z",
     "shell.execute_reply": "2021-05-29T12:50:43.711465Z",
     "shell.execute_reply.started": "2021-05-29T12:50:43.329229Z"
    }
   },
   "outputs": [],
   "source": [
    "new_schedual.to_excel('new_schedual_v2.0.xlsx')\n",
    "new_schedual.to_csv('new_schedual_v2.0.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
